IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'spCratosProject_RetrieveAll')
	BEGIN
		PRINT 'Dropping Procedure spCratosProject_RetrieveAll'
		DROP  Procedure  spCratosProject_RetrieveAll
	END

GO

PRINT 'Creating Procedure spCratosProject_RetrieveAll'
GO

CREATE Procedure spCratosProject_RetrieveAll
	(
		@StartRecord	bigint = null,
		@EndRecord		bigint = null
	)  
AS
		/* 
		------------------------------------------------------------------------------
		- Change History
		- --------------
		- Date                Description
		------------------------------------------------------------------------------
		- 25/06/2010 13:45:27 Initial version created
		------------------------------------------------------------------------------
		*/
	
	SET NOCOUNT ON
	
	-- Declare the relevant variables
	DECLARE @NoOfRecords bigint

	SELECT	@NoOfRecords = COUNT(*)
	FROM	  [CratosProject]
	
	-- Retrieve the relevant records
	IF (@StartRecord IS NOT NULL)
	BEGIN
		WITH AllRecords AS
		(
			SELECT	*,
							RowNumber = (ROW_NUMBER() OVER (ORDER BY [CratosProject].CratosProject_ID))
			FROM	  [CratosProject]
		)
		SELECT  *, @NoOfRecords AS 'NoOfRecords'
		FROM	  AllRecords
		WHERE   RowNumber BETWEEN @StartRecord AND @EndRecord
	END
	ELSE
	BEGIN
		SELECT  *, @NoOfRecords AS 'NoOfRecords'
		FROM	  [CratosProject]
	END

	-- Return to calling method
	RETURN
GO
